-- @owner: @peilinqian
-- @date: 2024/04/26
-- @testpoint: dblink事务相关操作验证

--test1：delete事务
--查询已有数据
select * from dblink('host=remote_ip port=remote_port dbname=remote_db1 user=remote_user_com password=remote_passwd','select * from remote_tb') AS t (a int, b text, c text[]);
--进行delete事务不提交
SELECT dblink_connect('myconn', 'host=remote_ip port=remote_port dbname=remote_db1 user=remote_user_com password=remote_passwd');
select dblink_exec('myconn','begin;delete from remote_tb where f1 =9');
--查询已有数据不变(事务所处连接中查询已变更，非事务所处连接查询数据未变更)
select * from dblink('myconn','select * from remote_tb') AS t (a int, b text, c text[]);
select * from dblink('host=remote_ip port=remote_port dbname=remote_db1 user=remote_user_com password=remote_passwd','select * from remote_tb') AS t (a int, b text, c text[]);
--进行事务提交
select dblink_exec('myconn','commit;');
--查询已有数据变更
select * from dblink('host=remote_ip port=remote_port dbname=remote_db1 user=remote_user_com password=remote_passwd','select * from remote_tb') AS t (a int, b text, c text[]);
-- close the named persistent connection
SELECT dblink_disconnect('myconn');

--test2：cursor
--创建连接
SELECT dblink_connect('myconn','host=remote_ip port=remote_port dbname=remote_db1 user=remote_user_com password=remote_passwd');
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
-- test opening cursor in a transaction
SELECT dblink_exec('myconn','BEGIN');
-- an open transaction will prevent dblink_open() from opening its own
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM remote_tb');
-- this should not commit the transaction because the client opened it
SELECT dblink_close('myconn','rmt_foo_cursor');
-- this should succeed because we have an open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM remote_tb');
-- commit remote transaction
SELECT dblink_exec('myconn','COMMIT');
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM remote_tb');
--test3：cursor
-- test automatic transactions for multiple cursor opens
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM remote_tb');
-- the second cursor
SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM remote_tb');
-- this should not commit the transaction
SELECT dblink_close('myconn','rmt_foo_cursor2');
-- this should succeed because we have an open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM remote_tb');
-- this should commit the transaction
SELECT dblink_close('myconn','rmt_foo_cursor');
-- this should fail because there is no open transaction
SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM remote_tb');
-- reset remote transaction state
SELECT dblink_exec('myconn','ABORT');
-- close the named persistent connection
SELECT dblink_disconnect('myconn');